package net.servir.capacidades.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import net.servir.capacidades.model.Etapa;
import net.servir.capacidades.model.FuncionLeyenda;
import net.servir.capacidades.model.StoredProcedureParameter;
import net.servir.capacidades.model.Enumerados.ExportCommandType;
import net.servir.capacidades.model.Enumerados.ParameterDirection;
import net.servir.capacidades.model.xls.XlsColumnConfig;
import net.servir.capacidades.model.xls.XlsTableConfig;
import net.servir.capacidades.model.xls.XlsWorksheetConfig;
import oracle.jdbc.OracleTypes;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class JdbcExportarExcelDAO {

	private SimpleJdbcTemplate jdbcTemplate;
	private DataSource dataSourceExport;

	@Autowired
	public JdbcExportarExcelDAO(DataSource dataSource) {
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.dataSourceExport = dataSource;
	}

	ExportExcelExtractor eee = new ExportExcelExtractor();
	 
	private HSSFSheet _xlsSheet;
	private HSSFWorkbook _wb;
	
	private XlsWorksheetConfig _xlsWorksheetConfig;

	public HSSFSheet FillExcelSheetWithQuery(String sql, HSSFSheet xlsSheet,
			HSSFWorkbook wb) {
		_xlsSheet = xlsSheet;
		_wb = wb;		

		Object obj = jdbcTemplate.getJdbcOperations().query(sql,
				new Object[] {}, eee);
		if (obj != null)
			return (HSSFSheet) obj;
		return null;
	}

	private class ExportExcelExtractor implements ResultSetExtractor {

		public HSSFSheet getXlsSheet() {
			return _xlsSheet;
		}

		public void setXlsSheet(HSSFSheet xlsSheet) {
			_xlsSheet = xlsSheet;
		}

		public Object extractData(ResultSet rs) throws SQLException,
				DataAccessException {
			return mapExcelRow(rs);
		}

	}

	private HSSFSheet mapExcelRow(ResultSet rs) throws SQLException {
		int iRow = 0;
		int iCols = rs.getMetaData().getColumnCount();
		List lColumnsType = new ArrayList();

		ResultSetMetaData rsmd = rs.getMetaData();

		// Definimos el tipo de fuente con la cual se mostrara la cabecera
		HSSFFont fuenteH = _wb.createFont();
		fuenteH.setFontHeightInPoints((short) 10);
		fuenteH.setFontName(fuenteH.FONT_ARIAL);
		fuenteH.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		// Creamos el objto HHSSFeclStyle que aplicara para el estilo a la celda
		HSSFCellStyle headerCellStyle = _wb.createCellStyle();
		headerCellStyle.setWrapText(false);
		headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		headerCellStyle.setFont(fuenteH);

		// Definimos los bordes de las celdas
		headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		headerCellStyle.setBottomBorderColor((short) 8);
		headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		headerCellStyle.setLeftBorderColor((short) 8);
		headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		headerCellStyle.setRightBorderColor((short) 8);
		headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		headerCellStyle.setTopBorderColor((short) 8);

		// Establecemos el tipo de sombreado de nuestra celda
		headerCellStyle.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT()
				.getIndex());
		headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// Para el contenido general
		HSSFFont fuenteC = _wb.createFont();
		fuenteC.setFontHeightInPoints((short) 9);
		fuenteC.setFontName(fuenteC.FONT_ARIAL);
		fuenteC.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		// Luego creamos el objeto que se encargara de aplicar el estilo a la
		// celda
		HSSFCellStyle dataCellStyle = _wb.createCellStyle();
		dataCellStyle.setFont(fuenteC);

		// Tambien, podemos establecer bordes...
		dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		dataCellStyle.setBottomBorderColor((short) 8);
		dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		dataCellStyle.setLeftBorderColor((short) 8);
		dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		dataCellStyle.setRightBorderColor((short) 8);
		dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		dataCellStyle.setTopBorderColor((short) 8);

		HSSFCellStyle dateCellStyle = _wb.createCellStyle();
		dateCellStyle.cloneStyleFrom(dataCellStyle);
		// dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/MM/yyyy"));
		dateCellStyle.setDataFormat((short) 15);

		HSSFRow row = _xlsSheet.createRow(iRow);

		for (int i = 1; i <= iCols; i++) {
			HSSFCell cell = row.createCell(i - 1);
			cell.setCellStyle(headerCellStyle);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			String columnName = rsmd.getColumnLabel(i).replace("_", " ");
			cell.setCellValue(columnName);
			lColumnsType.add(rsmd.getColumnTypeName(i));
		}
		while (rs.next()) {
			iRow++;
			row = _xlsSheet.createRow(iRow);
			for (int i = 1; i <= iCols; i++) {
				HSSFCell cell = row.createCell(i - 1);

				Object dato = rs.getObject(i);
				if (dato == null) {
					cell.setCellStyle(dataCellStyle);
					continue;
				}

				if ("NUMBER".equals(lColumnsType.get(i - 1))) {
					cell.setCellStyle(dataCellStyle);
					cell.setCellValue(rs.getDouble(i));
				} else if ("DATE".equals(lColumnsType.get(i - 1))) {
					// cell.setCellValue(new
					// HSSFRichTextString(rs.getString(i)));
					// cell.setCellValue(rs.getString(i));
					cell.setCellStyle(dateCellStyle);
					cell.setCellValue(rs.getDate(i));
				} else {
					cell.setCellStyle(dataCellStyle);
					cell.setCellValue(new HSSFRichTextString(rs.getString(i)));
				}
			}

		}

		return _xlsSheet;

	}

	public HSSFSheet FillExcelSheet(XlsWorksheetConfig xlsWorksheetConfig, HSSFSheet xlsSheet,
			HSSFWorkbook wb) {
		_xlsSheet = xlsSheet;
		_wb = wb;
		_xlsWorksheetConfig = xlsWorksheetConfig;

		for (XlsTableConfig xlsTableConfig : xlsWorksheetConfig.getTables()) {
			ExportExcelPlusExtractor xlsExtractor = new ExportExcelPlusExtractor(xlsTableConfig);
			if (xlsTableConfig.getCommand_type() == ExportCommandType.STORE_PROCEDURE)
			{
				try {
					xlsExtractor.extractData(this.getFromStoredProcedure(xlsTableConfig));					
				} catch (Exception e) {
					System.out.println("ERROR EXPORT FROM STORED PROCEDURE :"+e.getMessage());
				}				
			}				
			else
				jdbcTemplate.getJdbcOperations().query(xlsTableConfig.getSql(),new Object[] {}, xlsExtractor);
		}
		return _xlsSheet;
		/*Object obj = jdbcTemplate.getJdbcOperations().query(xlsWorksheetConfig.getSql(),
				new Object[] {}, xlsExtractor);
		if (obj != null)
			return (HSSFSheet) obj;
		return null;*/
	}
	private ResultSet getFromStoredProcedure(XlsTableConfig xlsTableConfig) throws SQLException
	{
		CallableStatement cstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		List listaRptEjecutoraCenso = new ArrayList();
		int i = 0;
		int indexCursorOut = 0;

		Object temp;
		
		StringBuilder sql = new StringBuilder();
		sql.append("{ call ").append(xlsTableConfig.getSql()).append("(");
		i = 0;
		for (StoredProcedureParameter param : xlsTableConfig.getParameters()) {
			i++;
			if (i== 1)
				sql.append("?");
			else
				sql.append(",?");				
		}		
		sql.append(")}");
		
		conn = this.dataSourceExport.getConnection();
		cstmt = conn.prepareCall(sql.toString());
		
		//Asignamos los valores de los parametros
		i = 0;
		for (StoredProcedureParameter param : xlsTableConfig.getParameters()) {
			i++;
			if (param.getDirection() == ParameterDirection.OUT)
			{
				switch (param.getData_type()) {
				case CURSOR:
					cstmt.registerOutParameter(i, OracleTypes.CURSOR);
					indexCursorOut = i;
					break;
				case INT:
					cstmt.registerOutParameter(i, OracleTypes.INTEGER);
					break;
				case FLOAT:
					cstmt.registerOutParameter(i, OracleTypes.FLOAT);
					break;
				case DATE:
					cstmt.registerOutParameter(i, OracleTypes.DATE);
					break;
				case STRING:
					cstmt.registerOutParameter(i, OracleTypes.VARCHAR);
					break;
				default:
					cstmt.registerOutParameter(i, OracleTypes.VARCHAR);
					break;
				}
			}
			else
			{
				switch (param.getData_type()) {
				case INT:
					cstmt.setInt(i, (Integer)param.getValue());
					break;
				case FLOAT:
					cstmt.setFloat(i, (Float)param.getValue());
					break;
				case DATE:
					cstmt.setDate(i, (Date)param.getValue());
					break;
				case STRING:
					cstmt.setString(i, param.getValue().toString());
					break;
				default:
					cstmt.setString(i, param.getValue().toString());
					break;
				}				
			}
		}
		cstmt.execute();
		rs = (ResultSet) cstmt.getObject(indexCursorOut);
		return rs;	
	}
	
	private class ExportExcelPlusExtractor implements ResultSetExtractor {

		private XlsTableConfig xlsTableConfig;
		
		public ExportExcelPlusExtractor(XlsTableConfig xlsTableConfig) {
			super();
			this.xlsTableConfig = xlsTableConfig;
		}

		public Object extractData(ResultSet rs) throws SQLException,
				DataAccessException {
			return mapExcelPlusRow(rs);
		}
		
		private HSSFSheet mapExcelPlusRow(ResultSet rs) throws SQLException {
			
			// Definimos el tipo de fuente con la cual se mostrara la cabecera
			HSSFFont fuenteH = _wb.createFont();
			fuenteH.setFontHeightInPoints((short) 10);
			fuenteH.setFontName(fuenteH.FONT_ARIAL);
			fuenteH.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			// Creamos el objto HHSSFeclStyle que aplicara para el estilo a la celda
			HSSFCellStyle headerCellStyle = _wb.createCellStyle();
			headerCellStyle.setWrapText(true);
			headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			headerCellStyle.setFont(fuenteH);

			// Definimos los bordes de las celdas
			headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headerCellStyle.setBottomBorderColor((short) 8);
			headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headerCellStyle.setLeftBorderColor((short) 8);
			headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headerCellStyle.setRightBorderColor((short) 8);
			headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			headerCellStyle.setTopBorderColor((short) 8);

			// Establecemos el tipo de sombreado de nuestra celda
			headerCellStyle.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT()
					.getIndex());
			headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

			// Para el contenido general
			HSSFFont fuenteC = _wb.createFont();
			fuenteC.setFontHeightInPoints((short) 9);
			fuenteC.setFontName(fuenteC.FONT_ARIAL);
			fuenteC.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

			// Luego creamos el objeto que se encargara de aplicar el estilo a la
			// celda
			HSSFCellStyle dataCellStyle = _wb.createCellStyle();
			dataCellStyle.setFont(fuenteC);

			// Tambien, podemos establecer bordes...
			dataCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			dataCellStyle.setBottomBorderColor((short) 8);
			dataCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			dataCellStyle.setLeftBorderColor((short) 8);
			dataCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			dataCellStyle.setRightBorderColor((short) 8);
			dataCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			dataCellStyle.setTopBorderColor((short) 8);

			HSSFCellStyle dateCellStyle = _wb.createCellStyle();
			dateCellStyle.cloneStyleFrom(dataCellStyle);
			// dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/MM/yyyy"));
			dateCellStyle.setDataFormat((short) 15);
			
			
			//Recuperamos la configuracion de las columnas
			ResultSetMetaData rsmd = rs.getMetaData();		
			
			if (xlsTableConfig.getColumns() == null || xlsTableConfig.getColumns().size() == 0)
			{
				List<XlsColumnConfig> list = new LinkedList<XlsColumnConfig>();
				//Configuramos las columnas del query 
				int iCols = rs.getMetaData().getColumnCount();
				for (int i = 1; i <= iCols; i++) {
					String columnName = rsmd.getColumnLabel(i).replace("_", " ");
					int iWidth = (int)(columnName.length());
					list.add(new XlsColumnConfig(rsmd.getColumnLabel(i),columnName,rsmd.getColumnTypeName(i),iWidth));
				}			
				xlsTableConfig.setColumns(list);				
				
			}		

			int iRow = xlsTableConfig.getRowStart() - 1;			
			int iColIni = xlsTableConfig.getColStart() - 1;			
			int iCol = iColIni - 1;
			

			//Generamos la cabecera del reporte			
			
			HSSFRow row = _xlsSheet.getRow(iRow);
			if (row == null )
				row = _xlsSheet.createRow(iRow);
			
			
			for (XlsColumnConfig xlsColumn : xlsTableConfig.getColumns()) {
				iCol++;
				HSSFCell cell = row.createCell(iCol);				
				cell.setCellStyle(headerCellStyle);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(xlsColumn.getColumn_header()));
				
				_xlsSheet.setColumnWidth(iCol, xlsColumn.getColumn_width()*256);
			}
			//Generamos los datos
			while (rs.next()) {
				iRow++;
				row = _xlsSheet.getRow(iRow);
				if (row == null )
					row = _xlsSheet.createRow(iRow);
				
				
				iCol = iColIni - 1;
				for (XlsColumnConfig xlsColumn : xlsTableConfig.getColumns()) {
					iCol++;
					HSSFCell cell = row.createCell(iCol);
					Object dato = rs.getObject(xlsColumn.getColumn_name());
					if (dato == null) {
						cell.setCellStyle(dataCellStyle);
						continue;
					}
					if ("NUMBER".equals(xlsColumn.getColumn_type())) {
						cell.setCellStyle(dataCellStyle);
						cell.setCellValue(rs.getDouble(xlsColumn.getColumn_name()));
					}
					else if ("INTEGER".equals(xlsColumn.getColumn_type())) {
						cell.setCellStyle(dataCellStyle);
						cell.setCellValue(rs.getInt(xlsColumn.getColumn_name()));
					}
					else if ("DATE".equals(xlsColumn.getColumn_type())) {
						cell.setCellStyle(dateCellStyle);
						cell.setCellValue(rs.getDate(xlsColumn.getColumn_name()));
					} else {
						cell.setCellStyle(dataCellStyle);
						cell.setCellValue(new HSSFRichTextString(rs.getString(xlsColumn.getColumn_name())));
					}					
				}					
			}
			return _xlsSheet;

		}		

	}

	public String queryFunciones(Integer idAmbito, String tipo) {
		String queryCenso = "";
		StringBuilder sql = new StringBuilder();
//		System.out.println("idAmbito : "+idAmbito);
//		System.out.println("tipo : "+tipo);
		sql.append("SELECT REPORTE.QUERY_REPORTE_CENSO(?,?) as QUERYCENSO FROM DUAL");
		queryCenso = this.jdbcTemplate.queryForObject(sql.toString(), String.class, idAmbito, tipo);
		return queryCenso;
	}
	
	 public List<Etapa> ObtenerFuncionesOrdenadas(Number idAmbito) {
			// Obtenemos los Sistemas y ambitos activos sobre el cual tiene acceso
			String sql = "Select e.id_etapa, e.descripcion as etapa, f.id_funcion, f.nemotecnico, f.descripcion as funcion "
					+ " from etapa e left join funcion f on e.id_etapa = f.id_etapa"
					+ " where e.id_ambito = ? " + " order by nemotecnico";
			List<Map<String, Object>> lSectorMaps = this.jdbcTemplate.queryForList(sql,
					idAmbito);
//			System.out.println("ObtenerFuncionesOrdenadas : " + sql);
			Number id_etapa_antes = 0;
			Number id_etapa = 0;
			Etapa etapa = null;
			FuncionLeyenda funcion = null;
			List<Etapa> etapasAmbito = new ArrayList<Etapa>();
			for (Map<String, Object> rec : lSectorMaps) {
				id_etapa = (Number) rec.get("ID_ETAPA");
				if (id_etapa.intValue() != id_etapa_antes.intValue()) {
					etapa = new Etapa();
					etapa.setId_etapa((Number) rec.get("ID_ETAPA"));
					etapa.setId_ambito(idAmbito);
					etapa.setDescripcion((String) rec.get("ETAPA"));
					etapa.setFunciones(new ArrayList<FuncionLeyenda>());

					etapasAmbito.add(etapa);
				}
				funcion = new FuncionLeyenda();
				funcion.setId_etapa((Number) rec.get("ID_ETAPA"));
				funcion.setId_funcion((Number) rec.get("ID_FUNCION"));
				funcion.setDescripcion((String) rec.get("FUNCION"));
				funcion.setNemotecnico((String) rec.get("NEMOTECNICO"));
				if (funcion.getId_funcion() != null)
					etapa.getFunciones().add(funcion);

				etapa.setNro_funciones(etapa.getFunciones().size());

				id_etapa_antes = id_etapa;
			}
			return etapasAmbito;

		}

}
